CentOS 7
Sponsored Link

SQL Server 2017 : Failover Cluster Instance
2017/10/15
 
Configure SQL Server Failover Cluster Instance.
This example is based on the environment below.
Configure SQL Server Failover Cluster Instance on ISCSI + CLVM (Clustered Logical Volume Manager) + GFS2 FileSystem. 
                        +--------------------+
                        | [node03.srv.world] |
                        |    ISCSI Target    |
                        +---------+----------+
                         10.0.0.53|
                                  |
+----------------------+          |          +----------------------+
| [ node01.srv.world ] |10.0.0.51 | 10.0.0.52| [ node02.srv.world ] |
|         CLVM         +----------+----------+          CLVM        |
|      SQL Server      |          |          |       SQL Server     |
+----------------------+          |          +----------------------+
                             VIP:10.0.0.50
[1]
Configure Cluster, refer to here. Finish to configure to [7] section. It's unnecessary to proceed to [8] or later on the link.)。
[2] Stop SQL Server on All Nodes and install SQL Server for HA package.
[root@node01 ~]#
systemctl stop mssql-server

[root@node01 ~]#
systemctl disable mssql-server

[root@node01 ~]#
yum -y install mssql-server-ha

[3] Add storage to Cluster resource and copy SQL Server's Data on it.
[root@node01 ~]#
cp -pR /var/opt/mssql/data /var/opt/mssql/data.bk
[root@node01 ~]#
pcs resource create fs_gfs2 Filesystem \
device="/dev/vg_cluster/lv_cluster" directory="/var/opt/mssql/data" fstype="gfs2" \
options="noatime,nodiratime" op monitor interval=10s on-fail=fence clone interleave=true
[root@node01 ~]#
pcs resource show 

 Clone Set: dlm-clone [dlm]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node01.srv.world node02.srv.world ]
[root@node01 ~]#
pcs constraint order start clvmd-clone then fs_gfs2-clone

Adding clvmd-clone fs_gfs2-clone (kind: Mandatory) (Options: first-action=start then-action=start)
[root@node01 ~]#
pcs constraint colocation add fs_gfs2-clone with clvmd-clone
[root@node01 ~]#
df -hT /var/opt/mssql/data 

Filesystem                        Type  Size  Used Avail Use% Mounted on
/dev/mapper/vg_cluster-lv_cluster gfs2   30G  431M   30G   2% /var/opt/mssql/data
[root@node01 ~]#
cp -pR /var/opt/mssql/data.bk/* /var/opt/mssql/data/

[root@node01 ~]#
ll /var/opt/mssql/data 

total 53504
-rw-r-----. 1 mssql mssql  4194304 Oct 16 15:44 master.mdf
-rw-r-----. 1 mssql mssql  2097152 Oct 16 15:44 mastlog.ldf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 modellog.ldf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 model.mdf
-rw-r-----. 1 mssql mssql 13959168 Oct 16 15:44 msdbdata.mdf
-rw-r-----. 1 mssql mssql   786432 Oct 16 15:44 msdblog.ldf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 tempdb.mdf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 templog.ldf
[root@node01 ~]#
rm -rf /var/opt/mssql/data.bk

[4] Start SQL Server and add an account for Pacemaker. Do it on a Node which mounts clustered storage.
[root@node01 ~]#
systemctl start mssql-server

[root@node01 ~]#
sqlcmd -S localhost -U SA

Password:

1> use master;
2> go

# add [hacluster] user
1> create login hacluster with PASSWORD= N'password';
2> go

# add [sysadmin] role
1> alter server role [sysadmin] add member hacluster;
2> go
1> exit
[root@node01 ~]#
systemctl stop mssql-server

[5] Add information of the account above for Pacemaker like follows on All Nodes.
[root@node01 ~]#
echo 'hacluster' > /var/opt/mssql/secrets/passwd

[root@node01 ~]#
echo 'password' >> /var/opt/mssql/secrets/passwd

[root@node01 ~]#
chown root. /var/opt/mssql/secrets/passwd

[root@node01 ~]#
chmod 600 /var/opt/mssql/secrets/passwd

[6] Add SQL Server and Virtual IP address to Cluster Resource. Do it on a Node.
[root@node01 ~]#
pcs resource create MSSQL_HA ocf:mssql:fci op start interval=60s --group SQL_Cluster

[root@node01 ~]#
pcs resource create VIP ocf:heartbeat:IPaddr2 ip=10.0.0.50 cidr_netmask=24 op monitor interval=30s --group SQL_Cluster

[root@node01 ~]#
pcs resource show 

 Clone Set: dlm-clone [dlm]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node01.srv.world node02.srv.world ]
 Resource Group: SQL_Cluster
     MSSQL_HA   (ocf::mssql:fci):       Started node01.srv.world
     VIP        (ocf::heartbeat:IPaddr2):       Started node01.srv.world
[root@node01 ~]#
pcs constraint order start fs_gfs2-clone then SQL_Cluster

Adding fs_gfs2-clone SQL_Cluster (kind: Mandatory) (Options: first-action=start then-action=start)
[root@node01 ~]#
pcs constraint colocation add SQL_Cluster with fs_gfs2-clone

[root@node01 ~]#
pcs constraint show 

Location Constraints:
Ordering Constraints:
  start dlm-clone then start clvmd-clone (kind:Mandatory)
  start clvmd-clone then start fs_gfs2-clone (kind:Mandatory)
  start fs_gfs2-clone then start SQL_Cluster (kind:Mandatory)
Colocation Constraints:
  clvmd-clone with dlm-clone (score:INFINITY)
  fs_gfs2-clone with clvmd-clone (score:INFINITY)
  SQL_Cluster with fs_gfs2-clone (score:INFINITY)
Ticket Constraints:
[7] It's OK all. Stop active node manualy and verify failover normally.
[root@node01 ~]#
pcs resource show 

 Clone Set: dlm-clone [dlm]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node01.srv.world node02.srv.world ]
 Resource Group: SQL_Cluster
     MSSQL_HA   (ocf::mssql:fci):       Started node01.srv.world
     VIP        (ocf::heartbeat:IPaddr2):       Started node01.srv.world
[root@node01 ~]#
sqlcmd -S 10.0.0.50 -U SA -Q 'select @@version'

Password:
-----------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
        Aug 22 2017 17:04:49
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

(1 rows affected)
# stop node01

[root@node01 ~]#
pcs cluster stop node01.srv.world

# on node02

[root@node02 ~]#
pcs resource show 

 Clone Set: dlm-clone [dlm]
     Started: [ node02.srv.world ]
     Stopped: [ node01.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node02.srv.world ]
     Stopped: [ node01.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node02.srv.world ]
     Stopped: [ node01.srv.world ]
 Resource Group: SQL_Cluster
     MSSQL_HA   (ocf::mssql:fci):       Started node02.srv.world
     VIP        (ocf::heartbeat:IPaddr2):       Started node02.srv.world
[root@node02 ~]#
sqlcmd -S 10.0.0.50 -U SA -Q 'select @@version'

Password:
-----------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
        Aug 22 2017 17:04:49
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

(1 rows affected)
 
Tweet